package com.android.database;




import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

public class GoodsServices {
	private GoodsDatabaseHelper dbHelper;
    private Context context;
	private static final String TABLE_NAME = "ST_Goods";// 数据表名

	public GoodsServices(Context context) {
		dbHelper = new GoodsDatabaseHelper(context);
        this.context = context;
	}

	//update ST_Goods set Number =replace(Number, ' ','') 去除表中字段的空格

	public void closeDB() {
		// 退出程序时关闭MyDatabaseHelper里的SQLiteDatabase
		if (dbHelper != null) {
			dbHelper.close();
		}
	}
	
	
	/**
	 * 添加一条数据
	 * 
	 * @param user
	 */
	public boolean insert(GoodInfo msg) {
		SQLiteDatabase sdb = dbHelper.getReadableDatabase();
				//typeID TEXT,typeNameChn TEXT,typeNameEng TEXT,foodID TEXT,foodNameChn TEXT,foodNameEng TEXT,foodPrice integer,foodPicPath TEXT
		String sql = "insert into ST_Goods(Number,Barcode,ProductName,Price,Qty,Flag) values(?,?,?,?,?,?)";
		Object obj[] = {msg.getNumber(), msg.getBarcode(),msg.getProductName(),msg.getPrices(),msg.getCount(),msg.getFlag()};

		try {
			sdb.execSQL(sql, obj);

		} catch (SQLException e) {
			//MyLog.i("err", "insert failed");
			sdb.close();
			return false;
		}

		sdb.close();
		// closeDB();
		return true;
	}


	/**
	 * 
	 * 删除所有数据
	 */
	public void deleteAll() {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {

			db.execSQL("delete from ST_Goods");

		} finally {
			db.close();
		}

	}

	
	/*//**
	 * 获取数据总数
	 * 
	 * @return
	 */
	public long getCount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from ST_Goods", null);
		cursor.moveToFirst();
		long reslut = cursor.getLong(0);
		cursor.close();
		db.close();
		return reslut;
	}
	
	// 查询某商品Number是否存在 (根据LineCode查询)
	public boolean goodsLineCodeExist(String number) throws Exception {
		SQLiteDatabase sdb = dbHelper.getReadableDatabase();
		String sql = "select * from ST_Goods where Number=?";
		Cursor cursor = sdb.rawQuery(sql, new String[] { number });
		try{
		if ( cursor!=null && cursor.moveToFirst() == true) {
			
			return true;
		}
		}
		finally{
			cursor.close();
		}
		return false;
	}
	

	// 查询某商品barcode是否存在 (根据barcode查询)
	public boolean goodsEnCodeExist(String barcode) throws Exception {
		SQLiteDatabase sdb = dbHelper.getReadableDatabase();
		String sql = "select * from ST_Goods where Barcode=?";
		Cursor cursor = sdb.rawQuery(sql, new String[] { barcode });
		try{
		if ( cursor!=null && cursor.moveToFirst() == true) {
			
			return true;
		}
		}
		finally{
			cursor.close();
		}
		return false;
	}
	
	
	/**
	 * 
	 * 检索
	 * @param Number
	 * @return
	 * @throws Exception
	 */
	public Cursor selectId(String Number) throws Exception{
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        //
        
        String sql = "SELECT * FROM ST_Goods where Number=? or Barcode=?";
        Cursor cursor = db.rawQuery(sql, new String[] { Number,Number});
        
        if (cursor != null && !cursor.isFirst()) {
                  cursor.moveToFirst();
        }
        
        //db.close();
        return cursor;
}
	
	
	/**
     * 更新一条数据
     * 盘点
     * @param user
     */
    public boolean update(String qty,String number) {
              SQLiteDatabase sdb = dbHelper.getReadableDatabase();//foodID
              
              String sql = "update ST_Goods set Qty=?,Flag=? where Number = ?";
              Object obj[] = { qty, '1',number};

              try {
                        sdb.execSQL(sql, obj);

              } catch (SQLException e) {
                       // MyLog.i("err", "insert failed");
                        sdb.close();
                        return false;
              }

              sdb.close();
              // closeDB();
              return true;
    }
	
	
	/**
     * 更新一条数据
     * 盘点
     * @param user
     */
    public boolean changCount(String qty,String number) {
              SQLiteDatabase sdb = dbHelper.getReadableDatabase();
              
              String sql = "update ST_Goods set Qty=? where Number = ?";
              Object obj[] = { qty,number};

              try {
                        sdb.execSQL(sql, obj);

              } catch (SQLException e) {
                       // MyLog.i("err", "insert failed");
                        sdb.close();
                        return false;
              }

              sdb.close();
              // closeDB();
              return true;
    }
    
    
	/**
     * 更新一条数据
     * 盘点
     * @param user
     */
    public boolean reset() {
              SQLiteDatabase sdb = dbHelper.getReadableDatabase();//foodID
              
              String sql = "update ST_Goods set Qty=?,Flag=?";
              Object obj[] = { '0', '0'};

              try {
                        sdb.execSQL(sql, obj);

              } catch (SQLException e) {
                       // MyLog.i("err", "insert failed");
                        sdb.close();
                        return false;
              }

              sdb.close();
              // closeDB();
              return true;
    }
    
    
    //select id from goods where Flag=1
    
	// TODO 查询已盘记录数量
	public  int getInventoryCount() throws SQLiteException{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		
		
		String sql = "select id from ST_Goods where Flag=1";
		Cursor cursor = db.rawQuery(sql, null);
		
		int count = 0;
		try{
			if (cursor != null) {
				cursor.moveToFirst();
				count = cursor.getCount();
			}
		}
		finally
		{
			cursor.close();
			db.close();
		}
		return count;
	}
	
	/**
	 * 
	 * 获取商品的总数
	 * @return
	 */
	public int getProductCount()
	{
		int reslut = 0;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("select Qty from ST_Goods", null);
		cursor.moveToFirst();
		while (!cursor.isLast()) {
			
			reslut = reslut+cursor.getInt(cursor.getColumnIndex("Qty"));
			cursor.moveToNext();
			
		}
		    reslut = reslut+cursor.getInt(cursor.getColumnIndex("Qty"));
		    cursor.close();
		db.close();
		return reslut;
	
		
	}
	

}
